package com.infore.water.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author: wk
 */
@Repository
public class DataDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<Map<String,Object>> getMuchHourStation(String stationString,String startTime,String endTime){
        String sql = "select a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg value,b.unit,b.cname from T_EnvHourData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id  where a.station_id in ("+stationString+") and a.time BETWEEN ? and ? order by a.time";
        return jdbcTemplate.queryForList(sql,startTime,endTime);
    }

    public List<Map<String,Object>> getMuchDayStation(String stationString, String startTime, String endTime) {
        String sql = "select a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg value,b.unit,b.cname from T_EnvDayData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id  where a.station_id in ("+stationString+") and a.time BETWEEN ? and ? order by a.time";
        return jdbcTemplate.queryForList(sql,startTime,endTime);
    }

    public List<Map<String,Object>> getAbnormalData(String stationId, String startTime, String endTime){
        if("0".equals(stationId)){
            String sql = "select a.equip_id stationId,c.name stationName,a.proj_name projectName,a.state,a.upper_standar upper,a.flower_standar flower,a.value,a.send_time time from T_DataAbnormal a left join T_EnvStation c on a.equip_id=c.id  where  a.send_time BETWEEN ? and ? order by a.send_time";
            return jdbcTemplate.queryForList(sql, startTime, endTime);
        }else {
            String sql = "select a.equip_id stationId,c.name stationName,a.proj_name projectName,a.state,a.upper_standar upper,a.flower_standar flower,a.value,a.send_time time from T_DataAbnormal a left join T_EnvStation c on a.equip_id=c.id  where a.equip_ID=? and a.send_time BETWEEN ? and ? order by a.send_time";
            return jdbcTemplate.queryForList(sql, stationId, startTime, endTime);
        }
    }

    public List<Map<String,Object>> getProjectArea(){
        String sql = "select id,name,code,cname,unit,Level_1,Level_2,Level_3,upper,flower from T_EnvProject";
        return jdbcTemplate.queryForList(sql);
    }

    public Integer setProjectArea(String projectId,String upper,String flower,String level_1,String level_2,String level_3){
        String sql = "update T_EnvProject SET upper=?,flower=?,level_1=?,level_2=?,level_3=? where id=?";
        return jdbcTemplate.update(sql,upper,flower,level_1,level_2,level_3,projectId);
    }

    public List<Map<String,Object>> getAbnormalDataByProject(String stationId, String startTime, String endTime,String projects) {
        if("0".equals(stationId)){
            String sql = "select a.equip_id stationId,c.name stationName,a.proj_name projectName,a.state,a.upper_standar upper,a.flower_standar flower,a.value,a.send_time time from T_DataAbnormal a left join T_EnvStation c on a.equip_id=c.id  where a.proj_id in ("+projects+") and a.send_time BETWEEN ? and ? order by a.send_time";
            return jdbcTemplate.queryForList(sql,stationId,startTime,endTime);
        }else {
            String sql = "select a.equip_id stationId,c.name stationName,a.proj_name projectName,a.state,a.upper_standar upper,a.flower_standar flower,a.value,a.send_time time from T_DataAbnormal a left join T_EnvStation c on a.equip_id=c.id  where a.equip_ID=? and a.proj_id in ("+projects+") and a.send_time BETWEEN ? and ? order by a.send_time";
            return jdbcTemplate.queryForList(sql,stationId,startTime,endTime);
        }
    }

    public List<Map<String,Object>> getRecentlyData(String stationId,String startTime,String endTime){
        String sql = "SELECT a.project_id projectId,a.proj_name projectName,a.station_id stationId,c.name stationName,a.time,a.avg,isnull(b.unit,'-') unit FROM T_EnvHourData a left join T_EnvProject b on a.project_id=b.id left join T_EnvStation c on a.station_id=c.id    where a.station_id=? and a.time>="+startTime+" and a.time<="+endTime+" order by a.time";
        return jdbcTemplate.queryForList(sql,stationId);
    }

    public Integer setAlarmStandard(Map<String,Object> map){
        KeyHolder keyHolder = new GeneratedKeyHolder();
        String sql = "insert into T_Base_ALArm_rule (ALARM_TYPE,ALARM_STEP,WORKDAY,[TIME],[INTERVAL],ALARMLEVEL,StationIDs) values(:alarmType,:alarmStep,:workDay,:time,:interval,:alarmLevel,:stationId)";
        namedParameterJdbcTemplate.update(sql,new MapSqlParameterSource(map), keyHolder);
        return keyHolder.getKey().intValue();
    }

}
